/* Dwarf_Schema.sql */

use master;

if not exists (select dbid from sysdatabases where name ='dwarf')
create database dwarf;
go

use dwarf;
go

---------------------------------------------------------------------------------------
create table dwarf
(
	dwarf_id	int		identity	primary key,
	dwarf_name	varchar(80)	unique		not null,
	born		int				not null,
	home_id		int				null,		/* mountain_id */
	spouse_id	int				null		/* dwarf_id */
);

alter table dwarf add
	constraint dwarf_spouse_fk foreign key (spouse_id)
	references dwarf (dwarf_id);

grant all on dwarf to public;
go

---------------------------------------------------------------------------------------
create table mountain
(
	mountain_id	int		identity	primary key,
	mountain_name	varchar(80)	unique		not null,
	location	varchar(80)			not null,
	king_id		int				null		/* dwarf_id */
);

alter table dwarf add
	constraint dwarf_home_fk foreign key (home_id)
	references mountain (mountain_id);

alter table mountain add
	constraint mountain_king_fk foreign key (king_id)
	references dwarf (dwarf_id);

grant all on mountain to public;
go

---------------------------------------------------------------------------------------
/* mountains visited by dwarf */
create table visit
(
	dwarf_id	int		not null,
	mountain_id	int		not null
);

alter table visit add
	constraint pk_visit
	primary key clustered (dwarf_id,mountain_id);
grant all on visit to public;

alter table visit add
	constraint visit_dwarf_fk foreign key (dwarf_id)
	references dwarf (dwarf_id);

alter table visit add
	constraint visit_mountain_fk foreign key (mountain_id)
	references mountain (mountain_id);
go